<?php
	require_once("Model/Member.php");
	class MemberCatalogModel
	{
		//sql
		private $sqlAddMember = "INSERT INTO member (first_name, last_name, social_nr) VALUES (?, ?, ?)";
		private $sqlGetAllMembersAndInfo = 	"SELECT m.*, COUNT(b.member_id)
											FROM member AS m LEFT OUTER JOIN boat AS b ON m.member_id = b.member_id
											GROUP BY m.member_id";
		private $sqlGetOneMember = "SELECT m.*, COUNT(b.member_id)
									FROM member AS m LEFT OUTER JOIN boat AS b ON m.member_id = b.member_id
									WHERE m.member_id = ?
									GROUP BY m.member_id";
		
		private $db = NULL;
        
        public function __construct(Database $db) 
        {
                $this->db =$db;
        }
		
		public function AddMember($fName, $lName, $sNumber)
		{
			$sqlQuery = $this->sqlAddMember;
			$stmt = $this->db->PrepareSql($sqlQuery);
			$stmt->bind_param("sss", $fName, $lName, $sNumber);
			$this->db->ExecuteQuery($stmt);
		}
		
		public function DeleteMember($id)
		{
		   	$this->db->InsertUpdateDeleteFromDb("DELETE FROM member WHERE member_id = '$id'");
		}
		
		public function EditMember($id, $fName, $lName, $sNumber)
		{
			$this->db->InsertUpdateDeleteFromDb("UPDATE member SET first_name = '$fName', last_name = '$lName', social_nr = '$sNumber' WHERE member_id = '$id'");  
		}
		
		public function GetMember($id)
		{
			$sqlQuery = $this->sqlGetOneMember;
			$stmt = $this->db->PrepareSql($sqlQuery);
			$stmt->bind_param("i", $id);
			$stmt = $this->db->ExecuteAndReturnQuery($stmt);
			$stmt->bind_result($mID, $fName, $lName, $sNumber, $nr_boats);
			
			$member = null;
			
			if($stmt->fetch())
			{
				$member = new Member($mID, $fName, $lName, $sNumber, $nr_boats);
				
			}
			
			$stmt->Close();
			return $member;
			
		}
		
		public function GetMembers()
		{
			$sqlQuery = $this->sqlGetAllMembersAndInfo;
			$stmt = $this->db->PrepareSql($sqlQuery);
			$stmt = $this->db->ExecuteAndReturnQuery($stmt);
			$stmt->bind_result($mID, $fName, $lName, $sNumber, $nr_boats);
			
			$members = new MemberArray();
			
			while($stmt->fetch())
			{
				$member = new Member($mID, $fName, $lName, $sNumber, $nr_boats);
				$members->Add($member);
			}
			
			$stmt->Close();
			return $members;
		}
	}
?>